﻿using System;
using System.Collections;
using System.Text;
using System.Linq;
using System.Drawing;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Data;
using System.Data.SqlClient;
using System.Data.Entity;
using System.ComponentModel.DataAnnotations;
using System.Collections.Generic;
using System.Windows.Forms;


namespace FraudMitigator
{
    static class Queries
    {
        //public static User user { private get; set; }

        public static DataTable editEntryFormQuery()
        {
            DataTable t = new DataTable();
            using (var context = new DataContext(Strings.GetConnectionString()))
            {
                Table<Account> account = context.GetTable<Account>();
                Table<Check> check = context.GetTable<Check>();
                Table<Bank> bank = context.GetTable<Bank>();

                var data = account
                    .Join(check,
                        a => a.AcctID,
                        c => c.CheckAcctID,
                        (a, c) => new { a, c })
                    .Where(z => z.a.AcctID == z.c.CheckAcctID)
                    .Join(bank,
                        a => a.a.AcctBankID,
                        b => b.BankID,
                        (a, b) => new { a, b })
                    .Where(z => z.a.a.AcctBankID == z.b.BankID)
                    .Select(z => new
                    {
                        z.a.a.AcctFName,
                        z.a.a.AcctLName,
                        z.a.a.AcctAddr,
                        z.a.a.AcctCity,
                        z.a.a.AcctState,
                        z.a.a.AcctZip,
                        z.a.a.AcctNumber,
                        z.b.BankRNum,
                        z.a.c.CheckNumber,
                        z.a.c.CheckDate,
                        z.a.c.CheckAmount,
                        z.a.c.CheckLetterSent
                    });

                CustomLINQtoDataSetMethods.CopyToDataTable(data, t, null);
            }

            return t;
        }

        public static DataTable mainWindowQuery()
        {
            DataTable t = new DataTable();
            using (var context = new DataContext(Strings.GetConnectionString()))
            {
                Table<Account> account = context.GetTable<Account>();
                Table<Check> check = context.GetTable<Check>();
                Table<Bank> bank = context.GetTable<Bank>();

                var data = account
                    .Join(check,
                        a => a.AcctID,
                        c => c.CheckAcctID,
                        (a, c) => new { a, c })
                    .Where(z => z.a.AcctID == z.c.CheckAcctID)
                    .Join(bank,
                        a => a.a.AcctBankID,
                        b => b.BankID,
                        (a, b) => new { a, b })
                    .Where(z => z.a.a.AcctBankID == z.b.BankID)
                    .Select(z => new
                    {
                        z.a.a.AcctFName,
                        z.a.a.AcctLName,
                        z.a.a.AcctAddr,
                        z.a.a.AcctCity,
                        z.a.a.AcctState,
                        z.a.a.AcctZip,
                        z.a.a.AcctNumber,
                        z.a.c.CheckNumber,
                        z.a.c.CheckAmount,
                        z.b.BankRNum,
                        z.b.BankName
                    });

                CustomLINQtoDataSetMethods.CopyToDataTable(data, t, null);
            }

            return t;
        }

        public static void addCheckQuery(CheckData data)
        {
            DataTable t = new DataTable();
            using (var context = new DataContext(Strings.GetConnectionString()))
            {
                Table<Check> checks = context.GetTable<Check>();
                Table<Account> accounts = context.GetTable<Account>();
                Table<Bank> banks = context.GetTable<Bank>();

                Account a = new Account
                {
                    AcctFName = data.firstName,
                    AcctLName = data.lastName,
                    AcctNumber = data.accountNum,
                    AcctAddr = data.address,
                    AcctCity = data.city,
                    AcctState = data.state,
                    AcctZip = data.zip
                };

                Bank b = new Bank
                {
                    BankRNum = data.routingNum,
                    BankName = data.bankName
                };

                Check c = new Check
                {
                    CheckAmount = Convert.ToDecimal(data.amount),
                    CheckDate = Convert.ToDateTime(data.date),
                    CheckLetterReceived = 0,
                    CheckLetterSent = 0,
                    CheckNumber = data.checkNum,
                    CheckCollected = false,
                    CheckEntryDateTime = DateTime.Now,
                    UserID = MainScreen.user.UserID
                };

                accounts.InsertOnSubmit(a);

                try
                {
                    context.SubmitChanges();
                }
                catch (Exception e)
                {
                    MessageBox.Show("The account entry failed.\n\n" + e.ToString(), "Add User Error");
                }

                banks.InsertOnSubmit(b);

                try
                {
                    context.SubmitChanges();
                }
                catch (Exception e)
                {
                    MessageBox.Show("The bank entry failed.\n\n" + e.ToString(), "Add User Error");
                }

                Account acct = accounts
                    .Where(z => z.AcctFName == data.firstName
                    && z.AcctLName == data.lastName
                    && z.AcctNumber == data.accountNum
                    && z.AcctZip == data.zip)
                    .FirstOrDefault();

                Bank bank = banks
                    .Where(z => z.BankName == data.bankName
                    && z.BankRNum == data.routingNum)
                    .FirstOrDefault();

                c.CheckAcctID = acct.AcctID;
                acct.AcctBankID = bank.BankID;

                checks.InsertOnSubmit(c);

                try
                {
                    context.SubmitChanges();
                }
                catch (Exception e)
                {
                    MessageBox.Show("The check entry failed.\n\n" + e.ToString(), "Add User Error");
                }
            }
        }

        public static DataTable editUsersFormQuery()
        {
            DataTable t = new DataTable();
            using (var context = new DataContext(Strings.GetConnectionString()))
            {
                Table<User> users = context.GetTable<User>();

                var data = users
                    .Select(z => new
                    {
                        z.UserFName,
                        z.UserLName,
                        z.UserUsername,
                        z.UserPassword,
                        z.UserAccessLevel
                    });

                CustomLINQtoDataSetMethods.CopyToDataTable(data, t, null);
            }

            return t;
        }

        public static DataTable getOpenIncidents()
        {
            DataTable t = new DataTable();
            using (var context = new DataContext(Strings.GetConnectionString()))
            {
                Table<Check> checks = context.GetTable<Check>();
                Table<Account> accounts = context.GetTable<Account>();

                var data = checks
                    .Where(z => z.Account.AcctID == z.CheckAcctID 
                    && z.CheckLetterSent != 3)
                    .Select(z => new
                    {
                        z.Account.AcctFName,
                        z.Account.AcctLName,
                        z.CheckAmount,
                        z.CheckLetterSent,
                        z.CheckLetterReceived
                    });

                CustomLINQtoDataSetMethods.CopyToDataTable(data, t, null);
            }

            return t;
        }

        public static void updateEditEntryQuery(EntryData data)
        {
            using (var context = new DataContext(Strings.GetConnectionString()))
            {
                Table<Check> check = context.GetTable<Check>();

                var query = check
                    .Where(z => z.Account.AcctFName == data.firstName
                    && z.Account.AcctCity == data.city
                    && z.Account.AcctLName == data.lastName
                    && z.Account.AcctNumber == data.accountNum
                    && z.Account.AcctState == data.state
                    && z.Account.AcctZip == data.zip
                    && z.Account.AcctAddr == data.address
                    && z.CheckNumber == data.checkNum
                    && z.CheckDate == Convert.ToDateTime(data.date)
                    && z.CheckAmount == Convert.ToDecimal(data.amount)
                    && z.Account.Bank.BankRNum == data.routingNum
                    && z.CheckLetterSent == Convert.ToInt32(data.letterSent))
                    .FirstOrDefault();

                query.Account.AcctFName = data.newFirstName;
                query.Account.AcctLName = data.newLastName;
                query.Account.AcctAddr = data.newAddress;
                query.Account.AcctCity = data.newCity;
                query.Account.AcctState = data.newState;
                query.Account.AcctZip = data.newZip;
                query.Account.AcctNumber = data.newAccountNum;
                query.CheckNumber = data.newCheckNum;
                query.CheckDate = Convert.ToDateTime(data.newDate);
                query.CheckAmount = Convert.ToDecimal(data.newAmount);
                query.CheckEntryDateTime = DateTime.Now;
                query.UserID = MainScreen.user.UserID;
                query.Account.Bank.BankRNum = data.newRoutingNum;
                query.CheckLetterSent = Convert.ToInt32(data.newLetterSent);

                try
                {
                    context.SubmitChanges();
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.ToString(), "Update Error");
                }
            }
        }

        public static void insertUserQuery(UserData data)
        {
            using (var context = new DataContext(Strings.GetConnectionString()))
            {
                User u = new User
                {
                    UserFName = data.firstName,
                    UserLName = data.lastName,
                    UserUsername = data.username,
                    UserPassword = data.password,
                    UserAccessLevel = data.accessLevel
                };

                Table<User> users = context.GetTable<User>();
                users.InsertOnSubmit(u);

                try
                {
                    context.SubmitChanges();
                }
                catch (Exception e)
                {
                    MessageBox.Show("The user entry failed.\n\n" + e.ToString(), "Add User Error");
                }
            }
        }

        public static void updateUserQuery(UserData data)
        {
            using (var context = new DataContext(Strings.GetConnectionString()))
            {
                Table<User> users = context.GetTable<User>();

                User u = users
                    .Where(z => z.UserFName == data.firstName
                    && z.UserLName == data.lastName
                    && z.UserUsername == data.username
                    && z.UserPassword == data.password
                    && z.UserAccessLevel == data.accessLevel)
                    .FirstOrDefault();

                u.UserFName = data.newFirstName;
                u.UserLName = data.newLastName;
                u.UserUsername = data.newUsername;
                u.UserPassword = data.newPassword;
                u.UserAccessLevel = data.newAccessLevel;

                try
                {
                    context.SubmitChanges();
                }
                catch (Exception e)
                {
                    MessageBox.Show("The user edit failed.\n\n" + e.ToString(), "Edit User Error");
                }
            }
        }

        public static decimal getTotalAmtToCollect()
        {
            decimal sum;

            using (var context = new DataContext(Strings.GetConnectionString()))
            {
                Table<Check> checks = context.GetTable<Check>();

                sum = checks
                    .Where(z => z.CheckCollected == false)
                    .Select(z => z.CheckAmount)
                    .Sum();
            }

            return sum;
        }

        public static decimal getTotalAmtCollected()
        {
            decimal sum;

            using (var context = new DataContext(Strings.GetConnectionString()))
            {
                Table<Check> checks = context.GetTable<Check>();

                sum = checks
                    .Where(z => z.CheckCollected == true)
                    .Select(z => z.CheckAmount)
                    .Sum();
            }

            return sum;
        }

        public static int getNumFirstLetterSent()
        {
            int numSent;

            using (var context = new DataContext(Strings.GetConnectionString()))
            {
                Table<Check> checks = context.GetTable<Check>();

                numSent = Convert.ToInt32(
                    checks
                    .Where(z => z.CheckLetterSent == 1)
                    .Select(z => z.CheckID)
                    .Count()
                );
            }

            return numSent;
        }

        public static int getNumSecondLetterSent()
        {
            int numSent;

            using (var context = new DataContext(Strings.GetConnectionString()))
            {
                Table<Check> checks = context.GetTable<Check>();

                numSent = Convert.ToInt32(
                    checks
                    .Where(z => z.CheckLetterSent == 2)
                    .Select(z => z.CheckID)
                    .Count()
                );
            }

            return numSent;
        }

        public static int getNumThirdLetterSent()
        {
            int numSent;

            using (var context = new DataContext(Strings.GetConnectionString()))
            {
                Table<Check> checks = context.GetTable<Check>();

                numSent = Convert.ToInt32(
                    checks
                    .Where(z => z.CheckLetterSent == 3)
                    .Select(z => z.CheckID)
                    .Count()
                );
            }

            return numSent;
        }

        public static int getNumOpenIncidents()
        {
            int numOpen;

            using (var context = new DataContext(Strings.GetConnectionString()))
            {
                Table<Check> checks = context.GetTable<Check>();

                numOpen = Convert.ToInt32(
                    checks
                    .Where(z => z.CheckCollected == false)
                    .Select(z => z.CheckID)
                    .Count()
                );
            }

            return numOpen;
        }

        public static int getNumClosedIncidents()
        {
            int numClosed;

            using (var context = new DataContext(Strings.GetConnectionString()))
            {
                Table<Check> checks = context.GetTable<Check>();

                numClosed = Convert.ToInt32(
                    checks
                    .Where(z => z.CheckCollected == true)
                    .Select(z => z.CheckID)
                    .Count()
                );
            }

            return numClosed;
        }

        public static int getNumTotalIncidents() 
        {
            int totIncidents;

            using (var context = new DataContext(Strings.GetConnectionString()))
            {
                Table<Check> checks = context.GetTable<Check>();

                totIncidents = Convert.ToInt32(
                    checks
                    .Select(z => z.CheckID)
                    .Count()
                );
            }

            return totIncidents;
        }

        public static int getNumLettersToSend()
        {
            int numToSend;

            using (var context = new DataContext(Strings.GetConnectionString()))
            {
                Table<Check> checks = context.GetTable<Check>();

                numToSend = Convert.ToInt32(
                    checks
                    .Select(z => z.CheckID)
                    .Count()
                );
            }

            return numToSend;
        }
    }
}
